package com.project.dao.Impl;

import com.project.bean.ClassBean;
import com.project.bean.CutPagBean;
import com.project.bean.RoomBean;
import com.project.bean.StudentBean;
import com.project.dao.IClassDao;
import com.project.dao.IStudentDao;
import org.springframework.stereotype.Repository;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import static com.project.bean.CutPagBean.PAGESIZE;
@Repository
public class StudentDaoImpl extends BaseDao implements IStudentDao {
    @Override
    public int countByRoom(int roomId) {
        this.setConnection();
        try {
            ps=con.prepareStatement("SELECT ss.fk_rooId, COUNT(ss.pk_stId) co FROM s_student ss WHERE ss.fk_rooId=? ");
            ps.setObject(1,roomId);
            rs= ps.executeQuery();
            if(rs.next()){
                int num=rs.getInt("co");
                return  num;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            this.closeConnection();
        }
        return 0;
    }

    @Override
    public int countByClass(int classId) {
        this.setConnection();
        try {
            ps=con.prepareStatement(" SELECT fK_classId, COUNT(pk_stId) s FROM  s_student WHERE fK_classId=?");
            ps.setObject(1,classId);
            rs= ps.executeQuery();
            if (rs.next()){
                int num=rs.getInt("s");
                return num;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            this.closeConnection();
        }

        return 0;
    }

    @Override
    public List<StudentBean> findStudentBYRoodId(Integer roomId) {
        List<StudentBean> list=new ArrayList<>();
        this.setConnection();
        try {
            ps=con.prepareStatement(" SELECT * FROM  s_student s,r_room r,c_class c WHERE s.fk_rooId= r.pk_roomId  AND   s.fk_classId=c.pk_cId AND fk_rooId=?");
            ps.setObject(1,roomId);
            rs=ps.executeQuery();
            while (rs.next()){
             list.add(this.getStudent());
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            this.closeConnection();
        }
        return list;
    }

    private StudentBean getStudent() {
        StudentBean studentBean = new StudentBean();
        try {
            studentBean.setId(rs.getInt("pk_stId"));
            studentBean.setName(rs.getString("s_name"));
            studentBean.setGender(rs.getString("s_gender"));
            studentBean.setPhone(rs.getString("s_phone"));
            ClassBean classBean = new ClassBean();
            classBean.setNameClass(rs.getString("c_name"));
            studentBean.setClassBean(classBean);
            RoomBean roomBean = new RoomBean();
            roomBean.setAddress(rs.getString("r_address"));
            studentBean.setRoomBean(roomBean);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return studentBean;
    }

    @Override
    public CutPagBean<StudentBean> finAll(int pageNo, String name, String className, String roomName) {
        CutPagBean<StudentBean> cutPageBean=new CutPagBean<>();
        this.setConnection();
        try {
            String sql="SELECT s.*,c.c_name,r.r_address FROM s_student s JOIN r_room r ON  s.fk_rooId= r.pk_roomId JOIN c_class c ON s.fK_classId=c.pk_cId WHERE 1=1 ";
            String dynaSql="";
            if (name !=null && !(name.equals(""))){
                dynaSql+="AND s.s_name LIKE '%"+name+"%' ";
            }

            if (className !=null && !(className.equals(""))){
                dynaSql+="AND c.c_name ='"+className+"' ";
            }

            if (roomName !=null && !(roomName.equals(""))){
                dynaSql+="AND r.r_address LIKE '%"+roomName+"%' ";
            }

            ps=con.prepareStatement(sql+dynaSql+" ORDER BY s.pk_stId limit ?,?");
            ps.setObject(1,(pageNo-1)*CutPagBean.PAGESIZE);
            ps.setObject(2,CutPagBean.PAGESIZE);
            rs=ps.executeQuery();
            while (rs.next()){
                cutPageBean.getList().add(this.getStudent());
            }

            ps=con.prepareStatement("SELECT count(s.pk_stId) num FROM s_student s JOIN r_room r ON  s.fk_rooId= r.pk_roomId JOIN c_class c ON s.fK_classId=c.pk_cId WHERE 1=1 "+dynaSql);
            rs=ps.executeQuery();
            if (rs.next()){
                cutPageBean.setCount(rs.getInt("num"));
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }finally {
            this.closeConnection();
        }
        return cutPageBean;
    }

    @Override
    public void addStudent(StudentBean studentBean) {
        this.setConnection();
        try {
            ps=con.prepareStatement("INSERT INTO s_student(s_name,s_gender,s_phone,s_faceImg,s_liveTime,fk_rooId,fK_classId)\n" +
                    "VALUES(?,?,?,?,?,?,?)");
            ps.setObject(1,studentBean.getName());
            ps.setObject(2,studentBean.getGender());
            ps.setObject(3,studentBean.getPhone());
            ps.setObject(4,studentBean.getFaceImg());
            ps.setObject(5,studentBean.getDate());
            ps.setObject(6,studentBean.getRoomBean().getId());
            ps.setObject(7,studentBean.getClassBean().getId());
            ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            this.closeConnection();
        }
    }

    @Override
    public void update(Integer userId, Integer roomId) {
        this.setConnection();
        try {
            ps=con.prepareStatement(" UPDATE s_student SET fk_rooId=? WHERE pk_stId=?");
            ps.setObject(1,roomId);
            ps.setObject(2,userId);
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            this.closeConnection();
        }
    }

    @Override
    public void del(Integer id) {
        this.setConnection();
        try {
            ps=con.prepareStatement(" DELETE FROM s_student WHERE pk_stId=?");
            ps.setObject(1,id);
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            this.closeConnection();
        }
    }

    @Override
    public StudentBean findById(Integer id) {
        this.setConnection();
        try {
           ps= con.prepareStatement("  \n" +
                   " SELECT * FROM s_student s,c_class c,r_room r WHERE s.fk_rooId =r.pk_roomId  AND s.fK_classId=c.pk_cId AND pk_stId=?");
           ps.setObject(1,id);
           rs=ps.executeQuery();
           if (rs.next()){
               StudentBean studentBean = new StudentBean();
               studentBean.setId(rs.getInt("pk_stId"));
               studentBean.setName(rs.getString("s_name"));
               studentBean.setGender(rs.getString("s_gender"));
               studentBean.setPhone(rs.getString("s_phone"));
               studentBean.setFaceImg(rs.getString("s_faceImg"));
               ClassBean classBean = new ClassBean();
               classBean.setNameClass(rs.getString("c_name"));
               RoomBean roomBean = new RoomBean();
               roomBean.setAddress(rs.getString("r_address"));
               studentBean.setRoomBean(roomBean);
               studentBean.setClassBean(classBean);
                return studentBean;
           }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            this.closeConnection();
        }
        return null;
    }

}
